import pymysql
import getCovDataFromUrl
import json

"""
连接数据库
"""


def get_conn():
    # 建立连接
    conn = pymysql.connect(host="localhost", user="root", password="123456", db="cov", charset="utf8")
    # 创建游标
    cursor = conn.cursor()
    # print(conn)
    return conn, cursor


"""
断开数据库连接
"""


def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()


"""
存储每日新增历史数据、每日累计数据
"""


def storage_History():
    conn, cursor = get_conn()
    data_list = getCovDataFromUrl.chinaDayList()
    chinaDayAdd = data_list['data']['chinaDayAddList']  # 中国每日新增历史数据
    chinaDay = data_list['data']['chinaDayList']  # 中国每日具体历史数据
    # provinceCompare = data_list['data']['provinceCompare']  # 各省份较上日数据变化
    # print(data_list)

    for eachDayAdd in chinaDayAdd:
        healRate = eachDayAdd['healRate']  # 治愈率
        localinfectionadd = eachDayAdd['localinfectionadd']  # 本地感染新增
        localConfirmadd = eachDayAdd['localConfirmadd']  # 本地确诊新增
        confirm = eachDayAdd['confirm']  # 新增确诊
        suspect = eachDayAdd['suspect']  # 新增疑似
        dead = eachDayAdd['dead']  # 新增死亡
        heal = eachDayAdd['heal']  # 新增治愈
        importedCase = eachDayAdd['importedCase']  # 新增输入病例
        infect = eachDayAdd['infect']  # 新增感染
        deadRate = eachDayAdd['deadRate']  # 死亡率
        date = eachDayAdd['date']
        year = eachDayAdd['y']

        # 存储每日新增数据
        try:
            sql = """INSERT INTO chinadayadd_history(year,
                                 date, deadRate, healRate, localinfectionadd, localConfirmadd, confirm, suspect, dead, heal, importedCase, infect)
                                 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
            cursor.execute(sql,
                           [year, date, deadRate, healRate, localinfectionadd, localConfirmadd, confirm, suspect, dead,
                            heal,
                            importedCase, infect])
            conn.commit()  # 提交事务
        except Exception as err:
            print(err)
            conn.rollback()  # 事务回滚

    # 存储历史每日累计数据
    for eachDay in chinaDay:
        dead = eachDay['dead']  # 死亡总数
        nowConfirm = eachDay['nowConfirm']  # 当日现存确诊
        importedCase = eachDay['importedCase']  # 截止当日输入病例总数
        date = eachDay['date']
        year = eachDay['y']
        deadRate = eachDay['deadRate']  # 死亡率
        healRate = eachDay['healRate']  # 治愈率
        noInfect = eachDay['noInfect']  # 无症状感染
        confirm = eachDay['confirm']  # 累计确诊
        heal = eachDay['heal']  # 累计治愈
        suspect = eachDay['suspect']
        nowSevere = eachDay['nowSevere']  # 现存严重
        localConfirm = eachDay['localConfirm']  # 本土确诊
        try:
            sql = """
            INSERT INTO chinaday_history(year,
            date, deadRate, healRate, nowConfirm, localConfirm, confirm, nowSevere, dead, heal, importedCase, noInfect, suspect)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s)
                 """
            cursor.execute(sql, [year, date, deadRate, healRate, nowConfirm, localConfirm, confirm, nowSevere, dead,
                                 heal, importedCase, noInfect, suspect])
            conn.commit()
        except Exception as err:
            print(err)
            conn.rollback()
    close_conn(conn, cursor)
    pass


"""
对当日据进行持久化存储
"""


def storage_Today():
    rs = getCovDataFromUrl.china_data()
    conn, cursor = get_conn()
    # 删除最近一次数据
    del_china = "DELETE  FROM chinatoday"
    del_province = "DELETE  FROM provincetoday"
    del_city = "DELETE  FROM citytoday"
    del_con = [del_china, del_province, del_city]
    try:
        # 执行SQL语句
        for del_ in del_con:
            cursor.execute(del_)
            # 提交修改
            conn.commit()
    except:
        print('Delete is error')
        conn.rollback()

    # 以下为国内总体情况
    lastUpdateTime = rs['data']['lastUpdateTime']  # 最近一次更新时间
    confirm = rs['data']['chinaTotal']['confirm']  # 国内累计确诊人数
    dead = rs['data']['chinaTotal']['dead']  # 国内累计死亡人数
    heal = rs['data']['chinaTotal']['heal']  # 国内累计治愈人数
    importedCase = rs['data']['chinaTotal']['importedCase']  # 境外输入
    localConfirm = rs['data']['chinaTotal']['localConfirm']  # 本土确诊
    noInfect = rs['data']['chinaTotal']['noInfect']  # 无症状感染
    nowConfirm = rs['data']['chinaTotal']['nowConfirm']  # 现存确诊人数
    nowSevere = rs['data']['chinaTotal']['nowSevere']  # 现存严重
    suspect = rs['data']['chinaTotal']['suspect']  # 疑似
    # 以下为国内较上日变化数
    confirmCompare = rs['data']['chinaAdd']['confirm']  # 较上日累计确诊
    deadCompare = rs['data']['chinaAdd']['dead']  # 较上日死亡
    healCompare = rs['data']['chinaAdd']['heal']  # 较上日治愈
    importedCaseCompare = rs['data']['chinaAdd']['importedCase']  # 较上日境外输入
    localConfirmCompare = rs['data']['chinaAdd']['localConfirm']  # 较上日本地确诊
    noInfectCompare = rs['data']['chinaAdd']['noInfect']  # 较上日无症状感染
    nowConfirmCompare = rs['data']['chinaAdd']['nowConfirm']  # 较上日累计确诊
    nowSevereCompare = rs['data']['chinaAdd']['nowSevere']  # 较上日病重
    suspectCompare = rs['data']['chinaAdd']['suspect']  # 较上日疑似

    # 国内当日情况持久化存储
    sql = """
            INSERT INTO chinatoday(lastUpdateTime, nowConfirm, localConfirm, confirm, 
                                   nowSevere, dead, heal, importedCase, noInfect, suspect,
                                   nowConfirmCompare, localConfirmCompare, confirmCompare,
                                   nowSevereCompare,deadCompare, healCompare, importedCaseCompare, 
                                   noInfectCompare, suspectCompare)
                                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s, %s, %s, %s, %s, %s, %s )
           """

    try:
        cursor.execute(sql, [lastUpdateTime, nowConfirm, localConfirm, confirm, nowSevere, dead,
                             heal, importedCase, noInfect, suspect, nowConfirmCompare, localConfirmCompare,
                             confirmCompare, nowSevereCompare, deadCompare, healCompare, importedCaseCompare,
                             noInfectCompare, suspectCompare])
        conn.commit()
    except Exception as err:
        print(err)
        conn.rollback()

    # 以下为各省市情况
    for child in rs['data']['areaTree'][0]['children']:
        # 各省（自治区）今日数据，该省当前总体情况
        nameP = child['name']
        confirmP = child['total']['confirm']  # 累计确诊
        deadP = child['total']['dead']  # 累计死亡
        healP = child['total']['heal']  # 累计治愈
        deadRateP = child['total']['deadRate']  # 死亡率
        healRateP = child['total']['healRate']  # 治愈率
        nowConfirmP = child['total']['nowConfirm']  # 现有确诊
        suspectP = child['total']['suspect']  # 疑似
        wzzP = child['total']['wzz']  # 无症状

        # 对省份的总体数据进行持久化存储
        try:
            sql = """INSERT INTO provincetoday(name,
                 deadRate, healRate, nowConfirm, confirm, dead, heal, noInfect, suspect)
                 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
            cursor.execute(sql, [nameP, deadRateP, healRateP, nowConfirmP, confirmP, deadP,
                                 healP, wzzP, suspectP])
            conn.commit()
        except:
            # print("Error" + Exception)
            conn.rollback()

        # 以下为省级以下地区信息
        for childP in child['children']:
            # 以下为该省级以下地区具体情况
            nameC = childP['name']
            # confirmTodayC = childP['today']['confirm']
            confirmC = childP['total']['confirm']  # 累计确诊
            deadC = childP['total']['dead']  # 累计死亡
            healC = childP['total']['heal']  # 累计治愈
            deadRateC = childP['total']['deadRate']  # 死亡率
            healRateC = childP['total']['healRate']  # 治愈率
            nowConfirmC = childP['total']['nowConfirm']  # 现有确诊
            suspectC = childP['total']['suspect']  # 疑似
            wzzC = childP['total']['wzz']  # 无症状
            # print(childP)

            # 对省份下级市的总体数据进行持久化存储
            try:
                sql = """
                             INSERT INTO citytoday(province,name,
                             deadRate, healRate, nowConfirm, confirm, dead, heal, noInfect, suspect)
                             VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                      """
                cursor.execute(sql, [nameP, nameC, deadRateC, healRateC, nowConfirmC, confirmC, deadC,
                                     healC, wzzC, suspectC])
                conn.commit()
            except Exception as err:
                print(err)
                conn.rollback()
    close_conn(conn, cursor)
    pass


"""
获取历史每日新增数据
"""


def getAddHistory():
    conn, cursor = get_conn()
    sql = "SELECT * FROM chinadayadd_history"
    add_list_date = []  # date, heal, dead, confirm, importedCase, healRate, deadRate
    add_list_heal = []
    add_list_dead = []
    add_list_confirm = []
    add_list_importedCase = []
    add_list_healRate = []
    add_list_deadRate = []
    add_list = []
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        # print(type(results))
        # print(results)
        for row in results:
            # year = row[0]
            date = row[1]
            deadRate = row[11]
            healRate = row[10]
            # localinfectionadd = row[4]
            # localConfirmadd = row[5]
            confirm = row[2]
            # suspect = row[7]
            dead = row[4]
            heal = row[5]
            importedCase = row[6]
            # infect = row[11]
            """
            add_list.append({"year": year, "date": date, "deadRate": deadRate, "healRate": healRate,
                             "localinfectionadd": localinfectionadd, "localConfirmadd": localConfirmadd,
                             "confirm": confirm,
                             "suspect": suspect,
                             "dead": dead,
                             "heal": heal,
                             "importedCase": importedCase,
                             "infect": infect
                             })
            """
            add_list_date.append(date)
            add_list_heal.append(heal)
            add_list_dead.append(dead)
            add_list_confirm.append(confirm)
            add_list_importedCase.append(importedCase)
            add_list_healRate.append(healRate)
            add_list_deadRate.append(deadRate)
        # 将数据提取出来，添加到list
        add_list.append(
            [add_list_date, add_list_heal, add_list_dead, add_list_confirm, add_list_importedCase, add_list_healRate,
             add_list_deadRate])
    except:
        print("Database is error,don't get data")
    close_conn(conn, cursor)
    print(add_list)
    return add_list


"""
获取每日累计历史数据
"""


def getDayHistory():
    conn, cursor = get_conn()
    sql = "SELECT * FROM chinaday_history"
    day_list = []
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        startTime = results[0][0] + "-" + results[0][1].split(".")[0] + '-' + results[0][1].split(".")[1]
        endTime = results[-1][0] + "-" + results[-1][1].split(".")[0] + '-' + results[-1][1].split(".")[1]
        startMonth = results[0][0] + "-" + results[0][1].split(".")[0]
        endMonth = results[-1][0] + "-" + results[-1][1].split(".")[0]
        print("start" + startTime)
        print("end" + endTime)
        # print(type(results))
        # print(results)
        for row in results:
            # year = row[0]
            # date = row[1]
            dead = row[6]
            # nowconfirm = row[3]
            # importedCase = row[4]
            # deadRate = row[5]
            # healRate = row[6]
            # noInfect = row[7]
            # localConfirm = row[8]
            confirm = row[4]
            heal = row[7]
            # suspect = row[11]
            # nowSevere = row[12]
            """
            day_list.append({"year": year, "date": date, "deadRate": deadRate, "healRate": healRate,
                             "nowconfirm": nowconfirm, "noInfect": noInfect,
                             "confirm": confirm,
                             "suspect": suspect,
                             "dead": dead,
                             "heal": heal,
                             "importedCase": importedCase,
                             "localConfirm": localConfirm,
                             "nowSevere": nowSevere
                             })
            """
            day_list.append({
                "data": [{"name": "累计确诊", "value": confirm},
                         {"name": "累计治愈", "value": heal},
                         {"name": "累计死亡", "value": dead}]
            })

            # day_list.append([date, heal, dead, confirm, importedCase, healRate, deadRate])
    except:
        print("Database is error,don't get data")
    close_conn(conn, cursor)
    call_list = json.dumps({"cdata": {"day": day_list,
                                      "startTime": startTime,
                                      "endTime": endTime,
                                      "startMonth": startMonth,
                                      "endMonth": endMonth}}, ensure_ascii=False)
    print(call_list)
    return call_list, {"Content-Type": "application/json"}


"""
获取当日国内数据
"""


def get_chinaday():
    conn, cursor = get_conn()
    sql = "SELECT * FROM chinatoday"
    today_list = []
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            lastUpdateTime = row[0]
            dead = row[5]
            nowconfirm = row[1]
            importedCase = row[7]
            noInfect = row[8]
            localConfirm = row[2]
            confirm = row[3]
            heal = row[6]
            suspect = row[4]
            nowSevere = row[9]
            deadCompare = row[14]
            nowconfirmCompare = row[10]
            importedCaseCompare = row[16]
            noInfectCompare = row[17]
            localConfirmCompare = row[11]
            confirmCompare = row[12]
            healCompare = row[15]
            suspectCompare = row[13]
            nowSevereCompare = row[18]

            today_list.append({"lastUpdateTime": lastUpdateTime,
                               "nowconfirm": nowconfirm, "noInfect": noInfect,
                               "confirm": confirm,
                               "suspect": suspect,
                               "dead": dead,
                               "heal": heal,
                               "importedCase": importedCase,
                               "localConfirm": localConfirm,
                               "nowSevere": nowSevere,
                               "nowconfirmCompare": nowconfirmCompare, "noInfectCompare": noInfectCompare,
                               "confirmCompare": confirmCompare,
                               "suspectCompare": suspectCompare,
                               "deadCompare": deadCompare,
                               "healCompare": healCompare,
                               "importedCaseCompare": importedCaseCompare,
                               "localConfirmCompare": localConfirmCompare,
                               "nowSevereCompare": nowSevereCompare
                               })
    except:
        print("Database is error,don't get data")
    close_conn(conn, cursor)
    print(today_list)
    return today_list[0]


"""
获取各省当日情况
"""


def get_provinceday():
    conn, cursor = get_conn()
    sql = "SELECT * FROM provincetoday"
    today_list_confirm = []
    today_list_nowconfirm = []
    today_list_suspect = []
    today_list_heal = []
    today_list_dead = []
    today_list_noInfect = []
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            name = row[0]
            confirm = row[1]
            dead = row[3]
            heal = row[4]
            # deadRate = row[8]
            # healRate = row[7]
            nowconfirm = row[6]
            suspect = row[7]
            noInfect = row[5]
            today_list_confirm.append({"name": name, "value": confirm})
            today_list_nowconfirm.append({"name": name, "value": nowconfirm})
            today_list_dead.append({"name": name, "value": dead})
            today_list_heal.append({"name": name, "value": heal})
            today_list_suspect.append({"name": name, "value": suspect})
            today_list_noInfect.append({"name": name, "value": noInfect})
    except:
        print("Database is error,don't get data")
    close_conn(conn, cursor)
    # print(today_list)
    call_list = json.dumps({"pdata": {'confirm': today_list_confirm,
                                      'nowconfirm': today_list_nowconfirm,
                                      'heal': today_list_heal,
                                      'dead': today_list_dead,
                                      'suspect': today_list_suspect,
                                      'noInfect': today_list_noInfect}}, ensure_ascii=False)
    return call_list, {"Content-Type": "application/json"}


"""
获取各市当日情况
"""


def get_cityday(pname):
    if pname == 'p':
        return get_provinceday()
    else:
        conn, cursor = get_conn()
        sql = "SELECT * FROM citytoday where province = %s"
        today_list_confirm = []
        today_list_nowconfirm = []
        today_list_suspect = []
        today_list_heal = []
        today_list_dead = []
        today_list_noInfect = []
        try:
            # 执行SQL语句
            cursor.execute(sql, pname)
            # 获取所有记录列表
            results = cursor.fetchall()
            for row in results:
                # province = row[0]
                name = row[1]
                confirm = row[2]
                dead = row[4]
                heal = row[5]
                # deadRate = row[9]
                # healRate = row[8]
                nowconfirm = row[7]
                suspect = row[3]
                noInfect = row[6]
                today_list_confirm.append({"name": name, "value": confirm})
                today_list_nowconfirm.append({"name": name, "value": nowconfirm})
                today_list_dead.append({"name": name, "value": dead})
                today_list_heal.append({"name": name, "value": heal})
                today_list_suspect.append({"name": name, "value": suspect})
                today_list_noInfect.append({"name": name, "value": noInfect})  # 为满足地图{name：xxx,value:xxxx}格式
        except:
            print("Database is error,don't get data")
        close_conn(conn, cursor)
        call_list = json.dumps({"cdata": {'confirm': today_list_confirm,
                                          'nowconfirm': today_list_nowconfirm,
                                          'heal': today_list_heal,
                                          'dead': today_list_dead,
                                          'suspect': today_list_suspect,
                                          'noInfect': today_list_noInfect}}, ensure_ascii=False)
        return call_list, {"Content-Type": "application/json"}


"""
获取某市当日情况
"""


def get_onecityday(pname, cname):
    conn, cursor = get_conn()
    sql = "SELECT * FROM citytoday where province = %s and name = %s"
    today_list_confirm = []
    today_list_nowconfirm = []
    today_list_suspect = []
    today_list_heal = []
    today_list_dead = []
    today_list_noInfect = []
    try:
        # 执行SQL语句
        cursor.execute(sql, [pname, cname])
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            # province = row[0]
            name = row[1]
            confirm = row[2]
            dead = row[4]
            heal = row[5]
            # deadRate = row[9]
            # healRate = row[8]
            nowconfirm = row[7]
            suspect = row[3]
            noInfect = row[6]
            today_list_confirm.append({"name": name, "value": confirm})
            today_list_nowconfirm.append({"name": name, "value": nowconfirm})
            today_list_dead.append({"name": name, "value": dead})
            today_list_heal.append({"name": name, "value": heal})
            today_list_suspect.append({"name": name, "value": suspect})
            today_list_noInfect.append({"name": name, "value": noInfect})  # 为满足地图{name：xxx,value:xxxx}格式
    except:
        print("Database is error,don't get data")
    close_conn(conn, cursor)
    call_list = json.dumps({"cdata": {'confirm': today_list_confirm,
                                      'nowconfirm': today_list_nowconfirm,
                                      'heal': today_list_heal,
                                      'dead': today_list_dead,
                                      'suspect': today_list_suspect,
                                      'noInfect': today_list_noInfect}}, ensure_ascii=False)
    return call_list, {"Content-Type": "application/json"}


"""
数据刷新
"""


def refresh():
    if getCovDataFromUrl.china_data()['data']['lastUpdateTime'] != get_chinaday()['lastUpdateTime']:
        print("Data is updating!!!")
        storage_History()
        storage_Today()
        return "over"
    else:
        print("Data exist!!!")
        return "over"


if __name__ == '__main__':
    # storage_History()
    # storage_Today()
    # getAddHistory()
    # getDayHistory()
    # get_chinaday()
    # get_provinceday()
    # get_cityday()
    refresh()
    pass
